﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DuLieu;
using System.Data.OleDb;

namespace DAO
{
    public static class DuLieuDAO
    {
        public static List<DuLieuDTO> TimDuLieuTheoMaDiaDiem(int maDiaDiem)
        {
            string dieukien = "where MaTenDiaDiem=" + maDiaDiem.ToString();
            return TimDuLieuTheoMa(dieukien);
        }
        public static List<DuLieuDTO> TimDuLieuTheoMaDichVu(int maDichVu)
        {
            string dieukien = "where MaDichVu=" + maDichVu.ToString();
            return TimDuLieuTheoMa(dieukien);
        }
        public static List<DuLieuDTO> TimDuLieuTheoMaPhuong(int maPhuong)
        {
            string dieukien = "where MaPhuong=" + maPhuong.ToString();
            return TimDuLieuTheoMa(dieukien);
        }
        public static List<DuLieuDTO> TimDuLieuTheoMaQuan(int maQuan)
        {
            string dieukien = "where MaQuanHuyen=" + maQuan.ToString();
            return TimDuLieuTheoMa(dieukien);
        }
        public static List<DuLieuDTO> TimDuLieuTheoMaDuong(int maDuong)
        {
            string dieukien = "where MaDuong=" + maDuong.ToString();
            return TimDuLieuTheoMa(dieukien);
        }

        public static List<DuLieuDTO> TimDuLieuTongHop(String tenDV, int maLoaiDV, int maDuong, int maPhuong, int maQuan)
        {
            List<DuLieuDTO> resultList = null;

            string sql = "SELECT MaDuLieu, MaDichVu, dl.MaTenDiaDiem, SoNha, MaDuong, MaPhuong, MaQuanHuyen, KinhDo, ViDo, ChuThich " +
                         "FROM DuLieu dl,TenDiaDiem tdd " +
                         "WHERE tdd.TenDiaDiem like '%" + tenDV + "%' and tdd.MaTenDiaDiem = dl.MaTenDiaDiem and ";

            string dichvu = "";
            string duong = "";
            string phuong = "";
            string quan = "";

            if (maLoaiDV != -1)
                dichvu = "MaDichVu = " + maLoaiDV + " and ";

            if (maDuong != -1)
                duong = "MaDuong = " + maDuong + " and ";

            if (maPhuong != -1)
                phuong = "MaPhuong = " + maPhuong + " and ";

            if (maQuan != -1)
                quan = "MaQuanHuyen = " + maQuan + " and ";

            sql += dichvu;
            sql += duong;
            sql += phuong;
            sql += quan;

            sql = sql.Remove(sql.Length - 5, 5);

            try
            {
                DataProvider.OpenConnection();

                resultList = new List<DuLieuDTO>();

                OleDbDataReader reader = DataProvider.ExecuteReader(sql);

                while (reader.Read())
                {
                    DuLieuDTO dl = new DuLieuDTO();
                    if (!reader.IsDBNull(0)) dl.MaDuLieu = reader.GetInt32(0);
                    if (!reader.IsDBNull(1)) dl.MaDichVu = reader.GetInt32(1);
                    if (!reader.IsDBNull(2)) dl.MaTenDiaDiem = reader.GetInt32(2);
                    if (!reader.IsDBNull(3)) dl.SoNha = reader.GetString(3);
                    if (!reader.IsDBNull(4)) dl.MaDuong = reader.GetInt32(4);
                    if (!reader.IsDBNull(5)) dl.MaPhuong = reader.GetInt32(5);
                    if (!reader.IsDBNull(6)) dl.MaQuan = reader.GetInt32(6);
                    if (!reader.IsDBNull(7)) dl.KinhDo = reader.GetDouble(7);
                    if (!reader.IsDBNull(8)) dl.ViDo = reader.GetDouble(8);
                    if (!reader.IsDBNull(9)) dl.ChuThich = reader.GetString(9);

                    resultList.Add(dl);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return resultList;
        }

        private static List<DuLieuDTO> TimDuLieuTheoMa(string dieukien)
        {
            List<DuLieuDTO> list = new List<DuLieuDTO>();
            string query = "select MaDuLieu,MaDichVu,MaTenDiaDiem,SoNha,MaDuong,MaPhuong,MaQuanHuyen,MaTinhThanh,KinhDo,ViDo,ChuThich " +
                            "from DULIEU " + dieukien;
            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    DuLieuDTO dl = new DuLieuDTO();
                    if (!reader.IsDBNull(0))
                    {
                        dl.MaDuLieu = reader.GetInt32(0);
                    }
                    if (!reader.IsDBNull(1))
                    {
                        dl.MaDichVu = reader.GetInt32(1);
                    }
                    if (!reader.IsDBNull(2))
                    {
                        dl.MaTenDiaDiem = reader.GetInt32(2);
                    }
                    if (!reader.IsDBNull(3))
                    {
                        dl.SoNha = reader.GetString(3);
                    }
                    if (!reader.IsDBNull(4))
                    {
                        dl.MaDuong = reader.GetInt32(4);
                    }
                    if (!reader.IsDBNull(5))
                    {
                        dl.MaPhuong = reader.GetInt32(5);
                    }
                    if (!reader.IsDBNull(6))
                    {
                        dl.MaQuan = reader.GetInt32(6);
                    }
                    if (!reader.IsDBNull(7))
                    {
                        dl.MaThanhPho = reader.GetInt32(7);
                    }
                    if (!reader.IsDBNull(8))
                    {
                        dl.KinhDo = reader.GetDouble(8);
                    }
                    if (!reader.IsDBNull(9))
                    {
                        dl.ViDo = reader.GetDouble(9);
                    }
                    if (!reader.IsDBNull(10))
                    {
                        dl.ChuThich = reader.GetString(10);
                    }
                    list.Add(dl);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return list;
        }
    }
}
